/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package CSISL4pk;

import java.awt.*;
import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
/**
 *
 * @author Josh
 */
public class ReqNewCaseNumSQL {
    private ResultSet rs;
    private int cID;
    private int cID2;
    private String timeNow;
    
    
    public ReqNewCaseNumSQL(){
    }
    public int begRequest(){
        //insert into cases(invld) values (no)
        timeNow = getDateTime();
        try
        {


            // create our mysql database connection
            String myDriver = "com.mysql.jdbc.Driver";
            String myUrl = "jdbc:mysql://www.db4free.net:3306/csisl";//www.freesql.org:3306/csisl";
            Class.forName(myDriver);
            Connection conn = DriverManager.getConnection(myUrl, "csisaltlake","cs2450");
            
            //
            // our SQL SELECT query. 
            // if you only need a few columns, specify them by name instead of using "*"
            String ins = "INSERT INTO cases (invld, lastUpdateTime) VALUES (0,'"+timeNow+"')";
            
            
            System.out.print(ins);
            // create the java statement
            Statement st = conn.createStatement();
            
            // execute the query
            st.execute(ins);

            st.close();      

        }
        catch (Exception e)
        {
            //print out no such name in db
            System.out.print(e);
             System.out.print("SQL 'Request New Number_1' ERROR!!!");
             return 0;
        }
        try{
            String myDriver = "com.mysql.jdbc.Driver";
            String myUrl = "jdbc:mysql://www.db4free.net:3306/csisl";//www.freesql.org:3306/csisl";//"jdbc:mysql://SQL09.FREEMYSQL.NET:3306/csisl";

            Class.forName(myDriver);
            Connection conn2 = DriverManager.getConnection(myUrl, "csisaltlake","cs2450");
            String query = "SELECT id FROM cases WHERE lastUpdateTime ='"+timeNow+"'";
            Statement st2 = conn2.createStatement();
            
            rs = st2.executeQuery(query);
            while (rs.next())
                {
                    cID = rs.getInt("id");
                }
            st2.close();
            cID2 = cID + 8620;  
     
        }
        catch (Exception e)
        {
            //print out no such name in db
            System.out.print(e);
             System.out.print("SQL 'Request New Number_2' ERROR!!!");
             return 0;
            
        }
        
        try{
            String myDriver = "com.mysql.jdbc.Driver";
            String myUrl = "jdbc:mysql://www.db4free.net:3306/csisl";//www.freesql.org:3306/csisl";//"jdbc:mysql://SQL09.FREEMYSQL.NET:3306/csisl";

            Class.forName(myDriver);
            Connection conn3 = DriverManager.getConnection(myUrl, "csisaltlake","cs2450");
            String ins2 = "UPDATE cases set caseNum ="+cID2+" where id ="+cID+"";
            System.out.print(ins2);
            Statement st3 = conn3.createStatement();
            
            st3.execute(ins2);
            st3.close();
            return (cID+8620); 
        }
        catch (Exception e)
        {
            //print out no such name in db
            System.out.print(e);
             System.out.print("SQL 'Request New Number_3' ERROR!!!");
             return 0;
        }
    }
    public void cancelReq()
    {
        try{
            String myDriver = "com.mysql.jdbc.Driver";
            String myUrl = "jdbc:mysql://www.db4free.net:3306/csisl";//www.freesql.org:3306/csisl";//"jdbc:mysql://SQL09.FREEMYSQL.NET:3306/csisl";
            Class.forName(myDriver);
            Connection conn = DriverManager.getConnection(myUrl, "csisaltlake","cs2450");
            String upD = "UPDATE cases set invld = 1 where id ="+cID+"";
            System.out.print(upD);
            Statement st3 = conn.createStatement();
            
            st3.execute(upD);
            st3.close();
            
        }
        catch (Exception e)
        {
            //print out no such name in db
            System.out.print(e);
             System.out.print("SQL 'Request Cancel New' ERROR!!!");
             
        }
    }
    public static String getDateTime() {
        DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
        Date date = new Date();
        return dateFormat.format(date);
    }
}
